﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;

namespace Pda_Total_Station
{
    public struct DataField
    {
        private string m_fieldName;
        private string m_fieldType;
        private string m_isNull;

        public string fieldName
        {
            get
            {
                return m_fieldName;
            }
        }

        public string fieldType
        {
            get
            {
                return m_fieldType;
            }
        }
        public string isNull
        {
            get
            {
                return m_isNull;
            }
        }

        public DataField(string fieldname, string fieldType, string isNull)
        {
            m_fieldName = fieldname;
            m_fieldType = fieldType;
            m_isNull = isNull;
        }
    }

    public enum DatabaseType
    {
        Access,
        SqlServer,
        Sqlce
    }

    public class DataBase
    {
        #region 数据成员
        private string m_ConnectionString;
        private string m_SQL;
        private DataAdapter m_Dapt;
        private DbConnection m_Connection;
        private DbCommand m_Command;
        private DataSet m_DataSet;
        private DataTable m_DataTable;
        private DatabaseType m_dbType;

        #endregion

        #region 公共属性
        public string ConnectionString
        {
            get
            {
                return m_ConnectionString;
            }
            set
            {
                m_ConnectionString = value;
            }
        }

        public string SQL
        {
            get
            {
                return m_SQL;
            }
            set
            {
                m_SQL = value;
            }
        }

        public DataAdapter DataAdapter
        {
            get
            {
                return m_Dapt;
            }
        }

        public DbConnection SqlDBConnection
        {
            get
            {
                return m_Connection;
            }
        }

        public DataSet DataSet
        {
            get
            {
                return m_DataSet;
            }
        }
        #endregion

        #region 构造函数
        public DataBase(DatabaseType dbType, string sConn)
        {
            try
            {
                m_ConnectionString = sConn;
                m_SQL = string.Empty;
                m_DataTable = new DataTable();
                if (dbType == DatabaseType.Sqlce)
                {
                    m_dbType = DatabaseType.Sqlce;
                    m_Dapt = new SqlCeDataAdapter();

                    m_DataSet = new DataSet();
                    m_Connection = new SqlCeConnection();
                    m_Command = new SqlCeCommand();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("初始化数据库类" + e.Message, "warning");
            }
        }

        #endregion

        #region 析构函数
        ~DataBase()
        {
            m_ConnectionString = string.Empty;
            m_SQL = string.Empty;
        }
        #endregion

        #region 私有函数/方法
        private void ReGetDataSet()
        {
            try
            {
                ReNewDataSet();
                ReNewDataAdapter();

                m_Dapt.Fill(m_DataSet);
            }
            catch (Exception a)
            {
                MessageBox.Show("数据库查询" + a.Message, "warning");
            }
        }

        private void SetDatasetToNull()
        {
            try
            {
                if (m_DataSet != null)
                {
                    m_DataSet.Clear();
                    m_DataSet.Dispose();
                    m_DataSet = null;
                }
            }
            catch (Exception a)
            {
                MessageBox.Show("清除数据集" + a.Message, "warning");
            }
        }

        private void SetDataAdapterToNull()
        {
            try
            {
                if (m_Dapt != null)
                {
                    m_Dapt = null;
                }
            }
            catch (Exception a)
            {
                MessageBox.Show("清除数据适配器" + a.Message, "warning");
            }
        }

        private void SetCommandToNull()
        {
            try
            {
                if (m_Command != null)
                {
                    m_Command.Dispose();
                    m_Command = null;
                }
            }
            catch (Exception a)
            {
                MessageBox.Show("清除数据命令集" + a.Message, "warning");
            }
        }

        private void SetConnectionToNull()
        {
            try
            {
                if (m_Connection != null)
                {
                    m_Connection.Close();
                    m_Connection.Dispose();
                    m_Connection = null;
                }
            }
            catch (Exception a)
            {
                MessageBox.Show("清除数据库连接" + a.Message, "warning");
            }
        }

        private void ReNewDataSet()
        {
            try
            {
                SetDatasetToNull();
                m_DataSet = new DataSet();
            }
            catch (Exception a)
            {
                MessageBox.Show("新生成一个空的数据集" + a.Message, "warning");
            }
        }

        private void ReNewDataAdapter()
        {
            try
            {
                SetDataAdapterToNull();
                if(m_dbType == DatabaseType.Sqlce)
                {
                    m_Dapt =new SqlCeDataAdapter(m_SQL,m_ConnectionString);
                    SqlCeCommandBuilder  cb =new SqlCeCommandBuilder((SqlCeDataAdapter)m_Dapt);
                }
            }
            catch (Exception a)
            {
                MessageBox.Show("新生成一个空的数据适配器" + a.Message, "warning");
            }
        }

        private void ReNewConnection()
        {
            try
            {
                SetConnectionToNull();

                if (m_dbType == DatabaseType.Sqlce)
                {
                    m_Connection = new SqlCeConnection();
                }
            }
            catch (Exception a)
            {
                MessageBox.Show("新生成一个新据库连接" + a.Message, "warning");
            }
        }

        private void ReNewCommand()
        {
            try
            {
                SetCommandToNull();

                if (m_dbType == DatabaseType.Sqlce)
                {
                    m_Command = new SqlCeCommand();
                }
               
            }
            catch (Exception a)
            {
                MessageBox.Show("新生成一个数据库命令集" + a.Message, "warning");
            }
        }
        #endregion

        #region 公共函数/方法
        //存储二进制类型数据
        public void StoreBinaryData()
        {
        }

        //检查表结构是否符合要求
        public bool CheckTalbeStructure(string sTableName, ArrayList aStructure)
        {
            return true;
        }

        //测试数据库连接是否正确
        public bool CheckConnection()
        {
            try
            {
                m_Connection.ConnectionString = m_ConnectionString;
                m_Connection.Open();
                if (m_Connection.State == ConnectionState.Open)
                {
                    m_Connection.Close();
                    return true;

                }
                else
                {
                    m_Connection.Close();
                    return false;
                }
            }
            catch (Exception a)
            {
                MessageBox.Show("检测数据库连接" + a.Message, "warning");
                return false;
            }
        }

        //从数据库中删除表
        public bool DeleteTable(string sTableName)
        {
            try
            {

                if (IsTableInDataBase(sTableName) == true)
                {
                    string sql = "Drop table [" + sTableName + "]";
                    ExecuteSQL(sql);
                }

                return true;
            }
            catch (Exception a)
            {
                MessageBox.Show("从数据库中删除表" + a.Message, "warning");
                return false;
            }
        }

        //从数据库中建立新表
        public bool CreateNewTable(string sTableName, ArrayList fields)
        {
            try
            {
                //首先判断数据库中是否已存在该表
                m_SQL = "CREATE TABLE " + sTableName + " (";
                for (int i = 0; i < fields.Count-1; i++)
                {
                    DataField field = (DataField)fields[i];
                    m_SQL += field.fieldName + " " + field.fieldType + " " + field.isNull + ",";
                }
                DataField field1 = (DataField)fields[fields.Count - 1];
                m_SQL += field1.fieldName + " " + field1.fieldType + " " + field1.isNull;
                m_SQL += ")";
                ExecuteSQL(m_SQL);
                return true;
            }
            catch (Exception a)
            {
                MessageBox.Show("创建一个新表" + a.Message, "warning");
                return false;
            }
        }

        //执行一个SQL语句
        public bool ExecuteSQL(string sSQL)
        {
            try
            {
                ReNewCommand();
                ReNewConnection();

                m_Connection.ConnectionString = m_ConnectionString;
                m_Connection.Open();

                m_Command.Connection = m_Connection;
                m_Command.Transaction = m_Connection.BeginTransaction();
                try
                {
                    if (sSQL != "")
                    {
                        m_Command.CommandText = sSQL;
                        m_Command.ExecuteNonQuery();
                    }
                    m_Command.Transaction.Commit();
                }
                catch (Exception a)
                {
                    m_Command.Transaction.Rollback();
                    m_Command.Dispose();
                    m_Connection.Close();
                    MessageBox.Show("SQL语句执行失败" + a.Message, "warning");
                    return false;
                }
                m_Connection.Close();
                m_Connection.Dispose();
                m_Connection = null;
                m_Command.Dispose();
                m_Command = null;
                return true;
            }
            catch (Exception a)
            {
                m_Command.Transaction.Rollback();
                m_Command.Dispose();
                m_Connection.Close();
                MessageBox.Show("SQL语句执行失败" + a.Message, "warning");
                return false;
            }
        }

        //执行一组SQL语句
        public bool ExecuteSQL(string[] sSQLs)
        {
            try
            {
                ReNewCommand();
                ReNewConnection();
                m_Connection.ConnectionString = m_ConnectionString;
                m_Connection.Open();
                m_Command.Connection = m_Connection;
                m_Command.Transaction = m_Connection.BeginTransaction();
                try
                {
                    if (sSQLs != null)
                    {
                        for (int i = 0; i < sSQLs.Length; i++)
                        {
                            if (sSQLs[i] != "")
                            {
                                m_Command.CommandText = sSQLs[i];
                                m_Command.ExecuteNonQuery();
                            }
                        }
                    }
                    m_Command.Transaction.Commit();
                }
                catch (Exception a)
                {
                    m_Command.Transaction.Rollback();
                    m_Command.Dispose();
                    m_Connection.Close();
                    MessageBox.Show("SQL语句执行失败" + a.Message, "warning");
                }
                m_Connection.Close();
                m_Connection.Dispose();
                m_Connection = null;
                m_Command.Dispose();
                m_Command = null;
                return true;
            }
            catch (Exception a)
            {
                m_Command.Transaction.Rollback();
                m_Command.Dispose();
                m_Connection.Close();
                MessageBox.Show("SQL语句执行失败" + a.Message, "warning");
                return false;
            }
        }

        //
        public bool ExecuteSQL(ArrayList aSQL)
        {
            try
            {
                ReNewCommand();
                ReNewConnection();
                m_Connection.ConnectionString = m_ConnectionString;
                m_Connection.Open();
                m_Command.Connection = m_Connection;
                m_Command.Transaction = m_Connection.BeginTransaction();

                try
                {
                    if (aSQL != null)
                    {
                        for (int i = 0; i < aSQL.Count; i++)
                        {
                            if (aSQL[i].ToString() != "")
                            {
                                m_Command.CommandText = aSQL[i].ToString();
                                m_Command.ExecuteNonQuery();
                            }
                        }
                    }
                    m_Command.Transaction.Commit();
                }
                catch (Exception a)
                {
                    m_Command.Transaction.Rollback();
                    m_Command.Dispose();
                    m_Connection.Close();
                    MessageBox.Show("SQL语句执行失败" + a.Message, "warning");
                    return false;
                }
                m_Connection.Close();
                m_Connection.Dispose();
                m_Connection = null;
                m_Command.Dispose();
                m_Command = null;
                return true;
            }
            catch (Exception a)
            {
                m_Command.Transaction.Rollback();
                m_Command.Dispose();
                m_Connection.Close();
                MessageBox.Show("SQL语句执行失败" + a.Message, "warning");
                return false;
            }
        }

        public bool IsColInTable(string sCol, string sTableName)
        {
            try
            {
                m_SQL = "select * from " + sTableName + " where 1=0";
                ReGetDataSet();
                for (int i = 0; i < m_DataSet.Tables[0].Columns.Count; i++)
                {
                    if (m_DataSet.Tables[0].Columns[i].ColumnName.Trim().ToLower() == sCol.Trim().ToLower())
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (Exception a)
            {
                MessageBox.Show(a.Message);
                return false;
            }
        }

        public bool IsColInTable(ArrayList aCols, string sTableName)
        {
            try
            {
                m_SQL = "select * from " + sTableName + " where 1=0";
                ReGetDataSet();
                for (int i = 0; i < aCols.Count; i++)
                {
                    string sElements = aCols[i].ToString();
                    bool bExist = false;
                    for (int j = 0; j < m_DataSet.Tables[0].Columns.Count; j++)
                    {
                        if (m_DataSet.Tables[0].Columns[j].ColumnName.Trim().ToLower() == sElements.Trim().ToLower())
                        {
                            bExist = true;
                            break;
                        }
                    }
                    if (bExist == false)
                    {
                        return false;
                    }
                }

                return true;
            }
            catch (Exception a)
            {
                MessageBox.Show(a.Message);
                return false;
            }
        }

        public bool IsTableInDataBase(string sTableName)
        {
            try
            {
                m_SQL = "select TABLE_NAME from Information_Schema.TABLES WHERE TABLE_TYPE = 'TABLE'";
                ReGetDataSet();
                if (m_DataSet.Tables[0].Rows.Count == 0)
                {
                    return false;
                }
                for (int i = 0; i < m_DataSet.Tables[0].Rows.Count; i++)
                {
                    if (m_DataSet.Tables[0].Rows[i][0].ToString().Equals(sTableName))
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (Exception a)
            {
                MessageBox.Show(a.Message);
                return false;
            }
        }

        public DataSet GetDataSet(string sSQL)
        {
            try
            {
                m_SQL = sSQL;
                DataSet DS = new DataSet();
                ReNewDataAdapter();
                m_Dapt.Fill(DS);
                return DS;
            }
            catch (Exception a)
            {
                MessageBox.Show("得到数据集" + a.Message, "warning");
                return null;
            }
        }

        public void UpdateDataSet(DataSet ds)
        {
            try
            {
                ReNewDataAdapter();
                m_Dapt.Update(ds);
            }
            catch (Exception a)
            {
                MessageBox.Show("更新数据集" + a.Message, "warning");
            }
        }

        /*
        public DataSet GetDataSet(string sSQL,string sTableName)
        {
            try
            {
                m_SQL = sSQL;
                DataSet DS = new DataSet();
                ReNewDataAdapter();
                m_Dapt.Fill(DS);
                return DS;
            }
            catch (Exception a)
            {
                MessageBox.Show("得到数据集" + a.Message, "warning");
                return null;
            }
        }*/

        public bool AddColumn(string sCol, string sTableName, string sColType)
        {
            if (!IsColInTable(sCol, sTableName))
            {
                string sSQL = "alter table " + sTableName + " add column " + sCol + " " + sColType;
                m_SQL = sSQL;
                ExecuteSQL(m_SQL);
                return true;
            }
            else
            {
                return false;
            }
        }

        public static bool ConnectToDataBase(string sDBType, string sProvider, string sServer, string sUser, string sPwd, string sSchema, bool bAuthen, ref DataBase dataBase)
        {
            try
            {
                bool bDBType = false;
                string sConnectionString = string.Empty;

                switch (sDBType.Trim().ToLower())
                {
                    case "access":
                        sConnectionString += "Provider = " + sProvider + ";";
                        sConnectionString += "Persist Security Info = False" + ";";
                        sConnectionString += "Data Source = " + sServer;
                        bDBType = false;
                        break;
                    case "sqlserver":
                        sConnectionString += "server = " + sServer + ";";
                        if (!bAuthen)
                        {
                            sConnectionString += "user id = " + sUser + ";";
                            sConnectionString += "password = " + sPwd + ";";
                        }
                        else
                            sConnectionString += "integrated security = true" + ";";
                        sConnectionString += "database = " + sSchema;
                        bDBType = true;
                        break;
                }

                DataBase uDataBase;
                if (!bDBType)
                {
                    uDataBase = new DataBase(DatabaseType.Access, sConnectionString);
                }
                else
                {
                    uDataBase = new DataBase(DatabaseType.SqlServer, sConnectionString);
                }
                if (uDataBase.CheckConnection() == true)
                {
                    dataBase = uDataBase;
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception a)
            {
                MessageBox.Show(a.Message);
                return false;
            }
        }
        public static bool ConnectToDataBase(string tablename , ref DataBase dataBase)
        {
            string localDbName = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase.ToString())
                + "\\" + tablename;
            
            string connStr = "Data Source=" + localDbName;
            DataBase uDataBase;
            try
            {
                if (!File.Exists(localDbName))
                {
                    SqlCeEngine eng = new SqlCeEngine(connStr);
                    eng.CreateDatabase();
                }
            }
            catch
            {
                
                return false;
            }
            uDataBase = new DataBase(DatabaseType.Sqlce, connStr);
            if (uDataBase.CheckConnection() == true)
            {
                dataBase = uDataBase;
                return true;
            }
            else
            {
                return false;
            }

 
        }
        public static bool ConnectToDataBase(string sDBType, string ip, string port, string sProvider, string sServer, string sUser, string sPwd, string sSchema, bool bAuthen, ref DataBase dataBase)
        {
            try
            {
                bool bDBType = false;
                string sConnectionString = string.Empty;

                switch (sDBType.Trim().ToLower())
                {
                    case "access":
                        sConnectionString += "Provider = " + sProvider + ";";
                        sConnectionString += "Persist Security Info = False" + ";";
                        sConnectionString += "Data Source = " + sServer;
                        bDBType = false;
                        break;
                    case "sqlserver":
                        sConnectionString += "Data Source = " + ip + ";Network Library = DBMSSOCN;";//"," + port +
                        //sConnectionString += "server = " + sServer + ";";
                        if (!bAuthen)
                        {
                            sConnectionString += "user id = " + sUser + ";";
                            sConnectionString += "password = " + sPwd + ";";
                            //sConnectionString += "integrated security = true" + ";";
                        }
                        else
                        {
                            sConnectionString += "integrated security = true" + ";";
                        }
                        sConnectionString += "Initial Catalog = " + sSchema;
                        bDBType = true;
                        break;
                }

                DataBase uDataBase;
                if (!bDBType)
                {
                    uDataBase = new DataBase(DatabaseType.Access, sConnectionString);
                }
                else
                {
                    uDataBase = new DataBase(DatabaseType.SqlServer, sConnectionString);
                }
                if (uDataBase.CheckConnection() == true)
                {
                    dataBase = uDataBase;
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception a)
            {
                MessageBox.Show(a.Message);
                return false;
            }
        }
        #endregion

    }

}
